from flask  import Flask
from flask_sqlalchemy   import SQLAlchemy
import config

app = Flask(__name__)
app.config.update({
    'DEBUG': True,
    'TEMPLATES_AUTO_RELOAD': True
})

# 1. 使用flask_sqlalchemy中的SQLAlchemy进行初始化.
app.config.from_object(config)
db = SQLAlchemy(app)

# 3. 测试连接数据库是否正常.
# db.create_all()

# 在数据库创建表的方法:
'''
# 用户表:
create table users(
    id int primary key autoincrement,
    username varchar(100) not null
);

# 文章表
create table article(
    id int primary key autoincrement,
    title varchar(100) not null,
    content text not null
    author_id int,
    foreign key `author_id references `users.id`
);
'''

# 创建orm模型
class User(db.Model):
    __tablename__='user'
    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    username = db.Column(db.String(100),nullable=False)


class Article(db.Model):
    __tablename__='article'
    id = db.Column(db.Integer,primary_key=True,autoincrement=True)
    title = db.Column(db.String(100),nullable=False)
    content = db.Column(db.Text,nullable=False)
    # author_id引用user表中的id,author_id就是User中的id
    author_id = db.Column(db.Integer,db.ForeignKey('user.id'))

    author = db.relationship('User',backref=db.backref('articles'))
    '''
     给`Article`这个模型添加一个`author`属性,可以访问这篇文章的作者的数据,像访问普通模型一样.
     - `backref`是定义反向引用,可以通过`User.articles`访问这个模型所写的所有文章.
    '''

db.create_all()  # 将orm模型的类映射为sql语句.

@app.route('/')
def index():
    return 'flask_sqlalchemy外键和关系映射'

@app.route('/Adduser/')
def ADDuser():
    # 添加一篇文章,文章必须依赖用户而存在. 首先需要添加一个用户.
    user1 = User(username='Jacky')
    db.session.add(user1)
    db.session.commit()
    return '添加用户成功'

@app.route('/AddArticle/')
def ADDarti():
    # 添加一篇文章
    article = Article(title='aaa',content='bbb',author_id=1)
    db.session.add(article)
    db.session.commit()

    return "添加文章成功"


@app.route('/SelectUser/') 
def SeleUser():
    # 要查找文章标题为aaa的作者
    # article = Article.query.filter(Article.title == 'aaa').first()
    # author_id = article.author_id
    # user = User.query.filter(User.id == author_id).first()
    # print(user.username)

    # article.author 
    # author = User.query.filter(User.username == 'Jacky').first()
    # author.articles

    # article = Article(title='aaa',content='bbb')
    # article.author = User.query.filter(User.id == 1).first()
    # db.session.add(article)
    # db.session.commit()

    # 要查找文章标题为aaa的这个作者
    article = Article.query.filter(Article.title == 'aaa').first()
    print('username:{}'.format(article.author.username))
    return "查找作者成功"


@app.route('/UserWriArti/')
def UserWrite():
    # 要找到Jacky这个用户写过的所有文章.
    article = Article(title='111',content='222',author_id=1)
    db.session.add(article)
    db.session.commit()
    user = User.query.filter(User.username == 'Jacky').first()
    result = user.articles
    for article in result:
        print('-'*10)
        print(article.title)

    return "Jacky用户写过的所有文章."

if __name__ == "__main__":
    app.run(debug=True)

